package pe.edu.upc.studio.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Collection;

import pe.edu.upc.studio.excepcion.DAOExcepcion;
import pe.edu.upc.studio.modelo.DetalleReserva;
import pe.edu.upc.studio.modelo.Sala;
import pe.edu.upc.studio.modelo.Articulo;
import pe.edu.upc.studio.modelo.GestorSala;
import pe.edu.upc.studio.util.ConexionBD;

public class ArmadoSalaDAO extends BaseDAO {

	public void insertarArmadoSala(GestorSala objGestorSala) throws DAOExcepcion {
		System.out.println("ArmadoSalaDAO: insertarArmadoSala(GestorSalaobjGestorSala)");
		String query = "INSERT INTO GestorSala(idArmado,idSala,idArticulo,CostoxHora,CantArticulo) VALUES (?,?,?,?,?)";
		Connection con = null;
		PreparedStatement stmt = null;
		try {
			con = ConexionBD.obtenerConexion();
			stmt = con.prepareStatement(query);
			stmt.setInt(1, objGestorSala.getIdArmado());
			stmt.setInt(2, objGestorSala.getIdSala().getIdSala());
			stmt.setInt(3, objGestorSala.getArticulo().getIdArticulo());
			stmt.setDouble(4, objGestorSala.getCostoHora());
			stmt.setInt(5, objGestorSala.getCantArticulo());
			int i = stmt.executeUpdate();
			if (i != 1) {
				throw new SQLException("No se pudo insertar");
			}
			
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
	}
	
	public Collection<Sala> buscarSalaArmado(String nombre, String local,
			int capacidad) throws DAOExcepcion {
		System.out.println("listarSalaArmado()");
		// Collection<GestorSala> lista = new ArrayList<GestorSala>();
		Collection<Sala> listasala = new ArrayList<Sala>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "Select a.idSala, b.Nombre, b.Local, b.Direccion, b.Capacidad, b.Capacidad, sum(a.CostoxHora * a.CantArticulo) "
					+ "From GestorSala a "
					+ "Inner Join Sala b On a.idSala = b.idSala "
					+ "Inner Join Articulo c On a.idArticulo = c.idArticulo "
					+ "Left Join Reserva d On a.idSala = d.idSala "
					+ "Where 1=1";

			if (nombre.length() > 0) {
				query += " And b.Nombre = '" + nombre + "'";
			}
			if (local.length() > 0) {
				query += " And b.Local = '" + local + "'";
			}			
			if (capacidad > 0) {
				query += " And b.Capacidad = " + capacidad;
			}
			query += " Group by a.idArmado";

			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				// GestorSala objGestorSala = new GestorSala();
				Sala objSala = new Sala();
				// Articulo objArticulo = new Articulo();
				// objGestorSala.setIdArmado(rs.getInt("idArmado"));
				/*
				 * objSala.setIdSala(rs.getInt("idSala"));
				 * objGestorSala.setIdSala(objSala);
				 * objArticulo.setIdArticulo(rs.getInt("idArticulo"));
				 * objGestorSala.setIdArticulo(objArticulo);
				 * objGestorSala.setCostoHora(rs.getDouble("CostoxHora"));
				 * objGestorSala.setCantArticulo(rs.getInt("CantArticulo"));
				 * lista.add(objGestorSala);
				 */

				objSala.setIdSala(rs.getInt("idSala"));
				objSala.setNombre(rs.getString("Nombre"));
				objSala.setLocal(rs.getString("Local"));
				objSala.setCapacidad(rs.getInt("Capacidad"));
				objSala.setDireccion(rs.getString("Direccion"));

				listasala.add(objSala);

			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return listasala;
	}

	public Collection<GestorSala> listarSalaArmado(String fecha, int idArmado)
			throws DAOExcepcion {
		System.out.println("listarSalaArmado()");
		Collection<GestorSala> lista = new ArrayList<GestorSala>();
		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		try {
			con = ConexionBD.obtenerConexion();
			String query = "Select a.idSala, b.Nombre, b.Local, b.Capacidad, b.Caracteristica,"
					+ "a.idArticulo,c.Tipo,a.CostoxHora,a.CantArticulo, a.CostoxHora * a.CantArticulo As Importe,d.Fecha "
					+ "From GestorSala a "
					+ "Inner Join Sala b On a.idSala = b.idSala "
					+ "Inner Join Articulo c On a.idArticulo = c.idArticulo "
					+ "Left Join Reserva d On a.idSala = d.idSala "
					+ "Where d.Fecha = '"
					+ fecha
					+ "'"
					+ "And a.idArmado="
					+ idArmado;
			stmt = con.prepareStatement(query);
			rs = stmt.executeQuery();
			while (rs.next()) {
				GestorSala objGestorSala = new GestorSala();
				Sala objSala = new Sala();
				Articulo objArticulo = new Articulo();
				// objGestorSala.setIdArmado(rs.getInt("idArmado"));
				objSala.setIdSala(rs.getInt("idSala"));
				objGestorSala.setIdSala(objSala);
				objArticulo.setIdArticulo(rs.getInt("idArticulo"));
				objGestorSala.setArticulo(objArticulo);
				objGestorSala.setCostoHora(rs.getDouble("CostoxHora"));
				objGestorSala.setCantArticulo(rs.getInt("CantArticulo"));
				lista.add(objGestorSala);
			}
		} catch (SQLException e) {
			System.err.println(e.getMessage());
			throw new DAOExcepcion(e.getMessage());
		} finally {
			this.cerrarStatement(stmt);
			this.cerrarConexion(con);
		}
		return lista;
	}

	public Collection<DetalleReserva> buscarDetalleSalaArmado(int idArmado)
	throws DAOExcepcion {
System.out.println(" buscarDetalleSalaArmado(int idArmado)");
Collection<DetalleReserva> lista = new ArrayList<DetalleReserva>();
Connection con = null;
PreparedStatement stmt = null;
ResultSet rs = null;
try {
	con = ConexionBD.obtenerConexion();
	String query = "Select a.idSala, a.idArticulo, a.CostoxHora, a.CantArticulo"
			+ "From GestorSala a "
			+ "Inner Join Sala b On a.idSala = b.idSala "
			+ "Inner Join Articulo c On a.idArticulo = c.idArticulo "
			+ "Where a.idArmado="
			+ idArmado;
	stmt = con.prepareStatement(query);
	rs = stmt.executeQuery();
	while (rs.next()) {
		DetalleReserva objDetalleReserva = new DetalleReserva();
		Articulo objArticulo = new Articulo();
		objArticulo.setIdArticulo(rs.getInt("idArticulo"));
		objArticulo.setCostoxHora(rs.getDouble("CostoxHora"));
		objArticulo.setCostoxHora(rs.getInt("CantArticulo"));
				objDetalleReserva.setArticulo(objArticulo);
		lista.add(objDetalleReserva);
	}
} catch (SQLException e) {
	System.err.println(e.getMessage());
	throw new DAOExcepcion(e.getMessage());
} finally {
	this.cerrarStatement(stmt);
	this.cerrarConexion(con);
}
return lista;
}
}
